package dao.mysql.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import dao.IReviewDao;
import dataModel.Review;

public class ReviewDao implements IReviewDao {

	private final String INSERT_REVIEW = "insert into review (productId, title, user, rating, datetime, url, goodQuality, badQuality, summary, usefulNum, uselessNum) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

	private final String SELECT_ALL_REVIEWS = "select * from review";

	@Override
	public void insert(Review review) {

		try {
			Connection conn = Configuration.getConnection();
			PreparedStatement stmt = conn.prepareStatement(INSERT_REVIEW);

			Integer productId = review.getProductId();
			String title = review.getTitle();
			String user = review.getUser();
			Integer rating = review.getRating();
			String datetime = review.getDatetime();
			String url = review.getUrl();
			String goodQuality = review.getGoodQuality();
			String badQuality = review.getBadQuality();
			String summary = review.getSummary();
			Integer usefulNum = review.getUsefulNum();
			Integer uselessNum = review.getUselessNum();

			productId = productId != null ? productId : 0;
			title = title != null ? title : "";
			user = user != null ? user : "";
			rating = rating != null ? rating : 0;
			// datetime = datetime != null ? datetime : "";
			url = url != null ? url : "";
			goodQuality = goodQuality != null ? goodQuality : "";
			badQuality = badQuality != null ? badQuality : "";
			summary = summary != null ? summary : "";
			usefulNum = usefulNum != null ? usefulNum : 0;
			uselessNum = uselessNum != null ? uselessNum : 0;

			stmt.setInt(1, productId);
			stmt.setString(2, title);
			stmt.setString(3, user);
			stmt.setInt(4, rating);
			stmt.setString(5, datetime);
			stmt.setString(6, url);
			stmt.setString(7, goodQuality);
			stmt.setString(8, badQuality);
			stmt.setString(9, summary);
			stmt.setInt(10, usefulNum);
			stmt.setInt(11, uselessNum);

			stmt.executeUpdate();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void selectAll(List<Object> reviews) {
		try {
			Connection conn = Configuration.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_ALL_REVIEWS);

			while (rs.next()) {
				Review review = new Review();
				review.setProductId(rs.getInt("productId"));
				review.setTitle(rs.getString("title"));
				review.setUser(rs.getString("user"));
				review.setDatetime(rs.getString("datetime"));
				review.setUrl(rs.getString("url"));
				review.setGoodQuality(rs.getString("goodQuality"));
				review.setBadQuality(rs.getString("badQuality"));
				review.setSummary(rs.getString("summary"));
				review.setRating(rs.getInt("rating"));
				review.setUsefulNum(rs.getInt("usefulNum"));
				review.setUselessNum(rs.getInt("uselessNum"));

				reviews.add(review);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public void selectAll(Map<Integer, List<Review>> reviews) {
		try {
			Connection conn = Configuration.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SELECT_ALL_REVIEWS);

			while (rs.next()) {
				Review review = new Review();

				int prodId = rs.getInt("productId");
				review.setProductId(prodId);

				review.setTitle(rs.getString("title"));
				review.setUser(rs.getString("user"));
				review.setDatetime(rs.getString("datetime"));
				review.setUrl(rs.getString("url"));
				review.setGoodQuality(rs.getString("goodQuality"));
				review.setBadQuality(rs.getString("badQuality"));
				review.setSummary(rs.getString("summary"));
				review.setRating(rs.getInt("rating"));
				review.setUsefulNum(rs.getInt("usefulNum"));
				review.setUselessNum(rs.getInt("uselessNum"));

				if (!reviews.containsKey(prodId))
					reviews.put(prodId, new ArrayList<Review>());
				reviews.get(prodId).add(review);
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
